﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Zor_Modes;
using Zor_IDAL;
using Zor_Tools;
using System.Data;
using Dapper;

namespace Zor_DAL
{
    public class Zor_Hous_DAL<T> where T : class
    {
        /// <summary>
        /// 商铺管理-修改
        /// </summary>
        /// <param name="hous">商铺对象</param>
        /// <returns>执行结果：1 成功 0失败</returns>
        public static BaseAnswer UpdateHous(Zor_Hous hous,Zor_NaturalPerson naturalPerson)
        {
            List<string> lstsql = new List<string>();

            //更新自然人表
            lstsql.Add(string.Format("update [Zor_NaturalPerson] set NatPer_Name='{0}',NatPer_Phone='{1}',NatPer_IDNumber='{2}',NatPer_Sex='{3}' where NatPer_Id=" + naturalPerson.NatPer_Id + "", SqlFilterHelper.FiltText(naturalPerson.NatPer_Name), SqlFilterHelper.FiltText(naturalPerson.NatPer_Phone), SqlFilterHelper.FiltText(naturalPerson.NatPer_IDNumber), naturalPerson.NatPer_Sex));

            //更新商铺信息表
            lstsql.Add(string.Format("update [Zor_Hous] set Uf_Id='{0}', Hs_Number='{1}',Shop_Longitude='{2}',Shop_Latitude='{3}',Hs_HousUse='{4}',Shop_TaxeMode='{5}',Hs_Proportion='{6}',Hs_attribute='{7}',Hs_Cqsx='{8}' where Hs_Id=" + hous.Hs_Id + "", hous.Uf_Id, hous.Hs_Number, hous.Shop_Longitude, hous.Shop_Latitude, hous.Hs_HousUse, hous.Shop_TaxeMode, hous.Hs_Proportion, hous.Hs_attribute, hous.Hs_Cqsx));

            BaseAnswer bar = DapperTransaction.DoTransaction(lstsql);                                                                                                                                                                                                                           
            return bar;                                                                                                                                                                                                                                                                         
        }
                                                                                                                                                                                                                                                                                                
        /// <summary>                                                                                                                                                                                                                                                                           
        /// 商铺管理-新增                                                                                                                                                                                                                                                                         
        /// </summary>                                                                                                                                                                                                                                                                          
        /// <param name="hous">商铺对象</param>                                                                                                                                                                                                                                                   
        /// <returns>执行结果：1 成功 0失败</returns>                                                                                                                                                                                                                                               
        public static BaseAnswer InsertHous(dynamic hous, Zor_NaturalPerson naturalPerson)
        {
            int dt = RequestSession.GetSessionUser().DT_Id;                                                                                                                                                                                                                                                                                
            List<string> lstsql = new List<string>();                                                                                                                                                                                                                                            
            var param = new DynamicParameters();                                                                                                                                                                                                                                                 
            param.Add("@NatPer_Id", naturalPerson.NatPer_Id);                                                                                                                                                                                                                                    
            param.Add("@NatPer_Name", naturalPerson.NatPer_Name);
            param.Add("@NatPer_Phone", naturalPerson.NatPer_Phone);
            param.Add("@NatPer_IDNumber", naturalPerson.NatPer_IDNumber);
            param.Add("@NatPer_Sex", naturalPerson.NatPer_Sex);
            param.Add("@User_Id", naturalPerson.NatPer_CreateName);
            param.Add("@CreateTime", naturalPerson.NatPer_CreateTime);
            param.Add("@Uf_Id ", hous.Uf_Id);
            param.Add("@Bd_Id ", hous.Bd_Id);
            param.Add("@Dt_Id ", dt);
            param.Add("@Hs_Number ", hous.Hs_Number);
            param.Add("@Shop_Longitude ", hous.Shop_Longitude);
            param.Add("@Shop_Latitude ", hous.Shop_Latitude);
            param.Add("@Hs_HousUse ", hous.Hs_HousUse);
            param.Add("@Shop_TaxeMode ", hous.Shop_TaxeMode);
            param.Add("@Hs_Proportion ", hous.Hs_Proportion);
            param.Add("@error", 0, DbType.Int32, ParameterDirection.ReturnValue);
            param.Add("@Hs_Cqsx ", hous.Hs_Cqsx);
            param.Add("@Hs_attribute ", hous.Hs_attribute);
            using (IDbConnection conn = DapperConn.OpenSqlConnection())
            {
                var Answer = conn.Execute("Zor_Shop", param, null, null, CommandType.StoredProcedure);
                return param.Get<int>("@error") > 0 ? new BaseAnswer { AnswerType = BaseAnswer.Answers.Succee } : new BaseAnswer { AnswerType = BaseAnswer.Answers.Failure };
            }

        }

        /// <summary>
        /// 删除商铺
        /// </summary>
        /// <param name="id">商铺Id集合</param>
        /// <returns></returns>
        public static BaseAnswer DeleteHous(int[] id)
        {
              
            var   dtSql = "select HouseShopId from [Zor_Lease] where HouseShopId in (" + string.Join(",", id).TrimEnd(',') + ")";
            List<dynamic> sqldelete =   DapperQuery<dynamic>.DoQueryList(dtSql, null);
            if (sqldelete.Count != 0)
            {
                return new BaseAnswer { AnswerType = BaseAnswer.Answers.Error, AnswerMeg = "房屋存在租赁关系，不能删除！" };
            }
            else
            {
                var dt = RequestSession.GetSessionUser().DT_Id;
                List<string> lstsql = new List<string>();

                var strSql = "select Dt_ParentId from Sys_Department where Dt_Id = " + dt + "";
                List<dynamic> listStId = DapperQuery<dynamic>.DoQueryList(strSql, null);

                int street = listStId[0].Dt_ParentId;

                var sql = "select Uf_Id,Bd_Id from V_Zor_Hous where Hs_Id in (" + string.Join(",", id).TrimEnd(',') + ")";
                List<dynamic> listUfId = DapperQuery<dynamic>.DoQueryList(sql, null);
                lstsql.Add(string.Format("delete Zor_Hous where Hs_Id in (" + string.Join(",", id).TrimEnd(',') + ")"));
                foreach (dynamic item in listUfId)
                {

                    lstsql.Add(string.Format("update [Zor_HouseCount] set Number = Number-1 where [Type]=1 and HousType = 1 and Uim_Id = " + item.Uf_Id + ""));
                    lstsql.Add(string.Format("update [Zor_HouseCount] set Number = Number-1 where [Type]=2 and HousType = 1 and Bd_Id = " + item.Bd_Id + ""));
                    lstsql.Add(string.Format("update [Zor_HouseCount] set Number = Number-1 where [Type]=3 and HousType = 1 and DT_Id = " + dt + ""));
                    lstsql.Add(string.Format("update [Zor_HouseCount] set Number = Number-1 where [Type]=4 and HousType = 1 and Stree_ID = " + street + ""));
                }
                BaseAnswer bar = DapperTransaction.DoTransaction(lstsql);
                return bar;
            }
        }

  

        /// <summary>
        /// 租赁信息-新增
        /// </summary>
        /// <param name="lease">租赁对象</param>
        /// <returns>执行结果：1 成功 0失败</returns>
        public static BaseAnswer InsertHsLease(Zor_Lease lease, DateTime startTime, DateTime endTime)
        {
            var st = TimeHelper.ToTimeStamp(startTime);      //租赁开始时间转时间戳格式
            var et = TimeHelper.ToTimeStamp(endTime);       //租赁结束时间转时间戳格式
            var sql = @"if not exists(select HouseShopId  from [V_Lease] where HouseShopId =@HouseShopId and statue = '正常租赁' ) 
                        insert into Zor_Lease values 
                        (@Tenant_Id,@HouseShopId,@Lease_StartTime,@Lease_EndTime,@Lease_Rent,@User_Id,@Lease_CreateTime,@Lease_Remarks,@Lease_Type)";

            BaseAnswer bar = DapperInsert<T>.DoInsert(sql, new
            {
                Tenant_Id = lease.Tenant_Id,
                HouseShopId = lease.HouseShopId,
                Lease_Type = 0,
                Lease_StartTime = st,
                Lease_EndTime = et,
                Lease_Rent = lease.Lease_Rent,
                Lease_Remarks = lease.Lease_Remarks,
                User_Id = RequestSession.GetSessionUser().User_Id,
                Lease_CreateTime = TimeHelper.ToTimeStamp(DateTime.Now)
            });
            return bar;
        }
        /// <summary>
        /// 获取所属门牌/单元名称
        /// </summary>
        /// <returns></returns>
        public static List<T> GetUimName(int id)
        {

            var sql = "select[Uim_Id],[Uim_Name] from [dbo].[Zor_UnitInfor] where BD_Id=" + id;
            return DapperQuery<T>.DoQueryList(sql, new { BD_Id = id });
        }

        /// <summary>
        /// 查询所有街道
        /// create light by 2017.4.5
        /// </summary>
        /// <returns></returns>
        public static List<T> SelectBdName()
        {
            var sql = "select Bd_Id,Bd_Name from dbo.Zor_Building where Bd_Id in (select  Bd_Id from Zor_Building where Dt_Id =" + RequestSession.GetSessionUser().DT_Id + " and Bd_Flage=1) order by Bd_Name";
            return DapperQuery<T>.DoQueryList(sql, null);
        }

    }
}
